#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from django.db.models.query import QuerySet
from web.dao.base_dao import BaseDao
from web.models.commodity_future_info import CommodityFutureInfo

"""
CommodityFutureInfo的dao类
"""


class CommodityFutureInfoDao(BaseDao):
    model_class = CommodityFutureInfo

    def add_oracle_column_comment(self):
        """
        为表commodity_future_info添加注释
        """

        with connection.cursor() as cursor:
            cursor.execute("comment on table commodity_future_info is \'商品期货信息表\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_info.id IS \'主键\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_info.code IS \'代码\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_info.name IS \'名称\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.commodity_future_exchange_id IS \'商品期货交易所id\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_info.night_trading_begin_hour IS \'夜盘交易开始时间\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_info.night_trading_end_hour IS \'夜盘交易结束时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_1_begin_hour IS \'日盘交易第一段开始时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_1_end_hour IS \'日盘交易第一段结束时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_2_begin_hour IS \'日盘交易第二段开始时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_2_end_hour IS \'日盘交易第二段结束时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_3_begin_hour IS \'日盘交易第三段开始时间\'")
            cursor.execute(
                "COMMENT ON COLUMN commodity_future_info.daily_trading_3_end_hour IS \'日盘交易第三段结束时间\'")

    def find_all(self) -> QuerySet:
        """
        查询所有记录
        """

        return self.find_list(dict(), dict(), list())

    def find_by_code(self, code: str) -> QuerySet:
        """
        根据code查询记录
        """

        filter_dict = {"code": code}
        return self.find_list(filter_dict, dict(), list())

    def find_for_hmm(self):
        """
        为隐马尔可夫算法收集数据
        """

        with connection.cursor() as cursor:
            # cursor.execute("select * from commodity_future_info t where t.code not in("
            #                "select distinct t1.code from mdl_hmm t1)")
            cursor.execute("select * from commodity_future_info t where t.code = 'PK'")
            _tuple = cursor.fetchall()
            commodity_future_info_list = list()
            for model in _tuple:
                commodity_future_info = CommodityFutureInfo()
                commodity_future_info.code = model[1]
                commodity_future_info.name = model[2]
                commodity_future_info_list.append(commodity_future_info)
            return commodity_future_info_list
            # model_instances = [CommodityFutureInfo(model) for model in _tuple]
            # return model_instances

    def concatenate_commodity_future_exchange_code_and_commodity_future_info_code_by_commodity_future_info_code(self, code):
        """
        根据code，链接commodity_future_exchange表的code字段和commodity_future_info表的code字段
        """

        with connection.cursor() as cursor:
            sql = ("select cfe.CODE || '.' || cfi.CODE "
                           "from COMMODITY_FUTURE_EXCHANGE cfe "
                           "join COMMODITY_FUTURE_INFO cfi on cfi.COMMODITY_FUTURE_EXCHANGE_ID=cfe.ID "
                           "where cfi.CODE='{}'").format(code)
            cursor.execute(sql)
            code_tuple = cursor.fetchall()
            if code_tuple is None:
                return None
            return code_tuple[0][0]
